#本文件从数据库中读取数据后，进行分析和展现
import pymysql,datetime
#连接数据库
conn=pymysql.connect(host='192.168.182.128',port=3306,user='root',
                     passwd='123456',db='stock_trans',charset='utf8')
cursor=conn.cursor()
#查询股票列表信息
sqlstr='select stock_code,stock_name from stocks_list'
cursor.execute(sqlstr)
stock_code_list=cursor.fetchall()
#转换日期
now_date=datetime.datetime.now().date()
delta_90=datetime.timedelta(days=90)
delta_180=datetime.timedelta(days=180)
delta_360=datetime.timedelta(days=360)
past_90=now_date-delta_90 #30天前
past_180=now_date-delta_180
past_360=now_date-delta_360
print(past_90,past_180,past_360)
sqlstr='select guPiaoCode,guPiaoName,min(shouPan),min(zuiDi) from daily_trans ' \
       'where riQi>="{}"AND riQi<="{}" GROUP BY guPiaoCode,guPiaoName'.format(past_180,now_date)
# print(sqlstr)
cursor.execute(sqlstr)
zuiDi_list=cursor.fetchall()
for row in zuiDi_list:
    # print(row)
    zuiDi_price=row[3]
    sqlstr='select * from daily_trans where guPiaoCode="{}" order by riQi DESC '.format(row[0])
    cursor.execute(sqlstr)
    last_trans=cursor.fetchone()
    # print(last_trans)
    last_price =last_trans[4]
    # print(last_price)
    #近180天最低价和最后一个交易日收盘价对比，输出价差在20%以内的股票信息
    if float(last_price)<=(float(zuiDi_price)*1.2):
        print(row[0],row[1],zuiDi_price,last_price)
